package com.mysql.test;

import org.junit.Test;

import java.sql.*;

/**
 * @author sunpeng
 * @Date 2021-07-02 17:17
 */

//注入攻击，预编译
public class Prepared {

    @Test
    public void query() throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://127.0.0.1:3306/mydatabase?useUnicode=true&characterEncoding=utf-8";
        Connection connection = DriverManager.getConnection(url, "root", "610527");

        String name = "guest";
        String password = "' or 'a' = 'a"; //注入攻击select * from account where name = 'guest' and password='' or 'a' = 'a'

        String sql = "select * from account where name = '"+name+"' and password='"+password+"'";
        System.out.println(sql);
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        if (resultSet.next()){
            System.out.println(true);
        }else{
            System.out.println(false);
        }
    }

    @Test
    public void preQuery() throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://127.0.0.1:3306/mydatabase?useUnicode=true&characterEncoding=utf-8";
        Connection connection = DriverManager.getConnection(url, "root", "610527");

        String name = "guest";
        String password = "' or 'a' = 'a"; //select * from account where name = ? and password = ?

        String sql = "select * from account where name = ? and password = ? ";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, name);
        preparedStatement.setString(2, password);
        ResultSet resultSet = preparedStatement.executeQuery();
        System.out.println(sql); //select * from account where name = ? and password = ?
        //最终sql: SELECT *   FROM users WHERE  userName = 'xiaoming'   AND password =  'anything\'   OR\'x\'=\'x\''
        //PreparedStatement会把 '变成 \' ，转义

        if (resultSet.next()){
            System.out.println(true);
        }else{
            System.out.println(false);
        }
    }

    /**
     * 预编译：增加
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    @Test
    public void add() throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.jdbc.Driver");
        //url, name, password, database, 参数
        String url = "jdbc:mysql://127.0.0.1:3306/mydatabase?useUnicode=true&characterEncoding=utf-8";
        Connection connection = DriverManager.getConnection(url, "root", "610527");

        String sql = "insert into student values(?, ?, ?, ?) ";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setLong(1, 106);
        preparedStatement.setString(2, "yi");
        preparedStatement.setDouble(3, 72);
        preparedStatement.setInt(4, 21);
        int i = preparedStatement.executeUpdate();
        System.out.println(i);

        preparedStatement.close();
        connection.close();
    }


}
